package com.fs.base.repository;

import java.math.BigInteger;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;

/**
 * @author TiddlerCJ
 * @desc jqa sql工具辅助
 */
@Transactional(readOnly=true)
@Repository
public class SqlRepository{
	@PersistenceContext
	private EntityManager entityManager;
	/**
	 * 设置参数
	 * @param query
	 * @param params
	 */
	@SuppressWarnings("unchecked")
	private void setParams(Query query,Object param,String sql){
		//System.out.println("sql:"+ sql);
		Assert.notNull(query, "query must not be null!");
		Map<Object, Object> data = new HashMap<Object,Object>();
		if (query!=null && param != null) {
			if(param instanceof Map){
				data = (Map<Object, Object>) param;
			}else{
				/*
				 *  QuoteFieldNames———-输出key时是否使用双引号,默认为true 
			        WriteMapNullValue——–是否输出值为null的字段,默认为false 
			        WriteNullNumberAsZero—-数值字段如果为null,输出为0,而非null 
			        WriteNullListAsEmpty—–List字段如果为null,输出为[],而非null 
			        WriteNullStringAsEmpty—字符类型字段如果为null,输出为”“,而非null 
			        WriteNullBooleanAsFalse–Boolean字段如果为null,输出为false,而非null 
				 */
				data = JSON.parseObject(JSON.toJSONString(param,SerializerFeature.WriteMapNullValue), Map.class);
			}
			for (Object key : data.keySet()) {
				/*
				 * 智能去除无用参数
				 */
				if((key instanceof Integer) || StringUtils.isNumeric(key.toString())){
					if(sql.indexOf("?"+key)>-1){
						query.setParameter(Integer.parseInt(key.toString()), data.get(key));
					}else{
						System.out.println("多余参数："+ key);
					}
				}else{
					if(sql.indexOf(":"+key)>-1){
						query.setParameter(key.toString(), data.get(key));
					}else{
						System.out.println("多余参数："+ key);
					}
				}
				
			}  
		}
	}
	
	/**
	 * 计数统计
	 * @param sql
	 * @param params
	 * @return
	 */
	public Long count(String sql, Object params){
		Assert.notNull(sql, "sql must not be null!");
		
		Query query =  entityManager.createNativeQuery(sql);
		setParams(query, params,sql);
		@SuppressWarnings("unchecked")
		List<Object> result = query.getResultList();
		Long total = 0L;  
		for (Object obj : result) {  
           BigInteger bigO = (BigInteger) obj;//转换成大类型  
           Long element = bigO.longValue(); //转换成long类型  
           total += element == null ? 0 : element;  
       }  
		return total;
	}

	/**
	 * 获取列表
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> list(String sql, Object params,Class<?> resultClass) {
		Assert.notNull(sql, "sql must not be null!");
		Query query = null;
		if(resultClass==null){
			query = entityManager.createNativeQuery(sql);
		}else{
			query = entityManager.createNativeQuery(sql,resultClass);
		}
		this.setParams(query, params,sql);
		return query.getResultList();
	}

	/**
	 * 获取分页数据
	 * 
	 * @param sql
	 * @param params
	 * @param pageable
	 * @return
	 */
    @SuppressWarnings("unchecked")
	public <T> Page<T> page(String sql,Map<Object, Object> params,Pageable pageable,Class<?> resultClass) {
    	Assert.notNull(sql, "sql must not be null!");
    	
		Page<T> page = null;
		boolean isPage = (pageable!=null);//分页标示
		String countSql = " select count(*) from (" + sql + ") as total ";

		/*
		 * 是否分页
		 */
        if (isPage) {
        	/*
             * 总记录数查询
             */
	        Long total = this.count(countSql, params);
        	/*
        	 * 分页查询封装
        	 */
	        Query query = null;
			if(resultClass==null){
				query = entityManager.createNativeQuery(sql);
			}else{
				query = entityManager.createNativeQuery(sql,resultClass);
			}
        	this.setParams(query, params,sql);
        	
        	query.setFirstResult(pageable.getOffset());  
        	query.setMaxResults(pageable.getPageSize());  
        	
        	 List<T> content = Collections.emptyList();
        	if(total>pageable.getOffset()){
        		 content = query.getResultList();
        	}
        	page = new PageImpl<T>(content, pageable, total);  
        }else{
        	List<T> list = list(sql, params,resultClass);
        	page = new PageImpl<T>(list);
        }
        return page; 
    }
    
    /**
     * sql执行器
     * @param sql
     * @return
     */
    @Transactional
    public Boolean excute(String sql){
    	Assert.notNull(sql, "sql must not be null!");
    	boolean flag = true;
    	try {
    		Query query = entityManager.createNativeQuery(sql);
    		query.executeUpdate();
    	} catch (Exception e) {
    		e.printStackTrace();
    		flag = false;
    	}
    	return flag;
    }
    
    /**
     * 
     * @param sql
     * @param params 实体参数对象
     * @return
     */
    @Transactional
    public<T> Boolean excute(String sql,Object params){
    	Assert.notNull(sql, "sql must not be null!");
    	boolean flag = true;
    	try {
    		Query query = entityManager.createNativeQuery(sql);
    		if(params!=null){
    			this.setParams(query, params,sql);
    		}
    		query.executeUpdate();
    	} catch (Exception e) {
    		e.printStackTrace();
    		flag = false;
    	}
    	return flag;
    }
    
    /**
     * 实体参数集合
     * @param sql
     * @param list
     * @return
     */
    @Transactional
    public<T> Boolean excute(String sql,List<T>list){
    	Assert.notNull(sql, "sql must not be null!");
    	boolean flag = true;
    	try {
    		for(T entity: list){
    			excute(sql, entity);
    		}
		} catch (Exception e) {
			e.printStackTrace();
			flag = false;
		}
    	return flag;
    }
}
